/*
--| This section is commented out to prevent accidental deletion of existing stored procedure's.
--| This can be uncommented after the stored procedure is created and confirmed that another stored procedure
--| with the same name does not exists.
--|
--| Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'au_getStatusEnum' 
)
   DROP PROCEDURE dbo.au_getStatusEnum
GO

*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--|--------------------------------------------------------------------------------------------------------------
--|Name:			au_getStatusEnum
--|Author:			Jamie Swindall
--|Description:		Gets an enumeration of viewable Student Statuses.
--|-------------------------------------------------------------------------------------------------------------- 
--|-------------------------- Parameters ------------------------------------------------------------------------
--|--------------------------------------------------------------------------------------------------------------
--| Name		||	Data Type	||	Default Value	||	Description
--|--------------------------------------------------------------------------------------------------------------
--|@ProgramId || INT || 0 || Determines which Program's Student Statuses to return. Default is 0 which returns
--		Student Statuses for all Programs. 
--|
--|--------------------------------------------------------------------------------------------------------------
--|--------------------------- Revision Information	-------------------------------------------------------------				 
--|--------------------------------------------------------------------------------------------------------------
--|	Author:				Date			Change					Description							SCR #
--|--------------------------------------------------------------------------------------------------------------
--|	Jamie Swindall		03/21/2011	Initial version						
--|														
--|--------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.au_getStatusEnum
	@ProgramId INT = 0
AS
BEGIN
	SET NOCOUNT ON;
	
	IF @ProgramId = 0
		BEGIN
			SELECT
					[dbo].[au_StudentCurriculumStatus].[Id], [dbo].[au_StudentCurriculumStatus].[Description]
			FROM
					[dbo].[au_StudentCurriculumStatus]
			WHERE
					[dbo].[au_StudentCurriculumStatus].[IsViewable] = 1
			ORDER BY
					[dbo].[au_StudentCurriculumStatus].[ViewOrder]
		END
	ELSE
		BEGIN
			SELECT
					[dbo].[au_StudentCurriculumStatus].[Id], [dbo].[au_StudentCurriculumStatus].[Description]
			FROM
					[dbo].[au_StudentCurriculumStatus]
			WHERE
					[dbo].[au_StudentCurriculumStatus].[ProgramId] = @ProgramId
			AND
					[dbo].[au_StudentCurriculumStatus].[IsViewable] = 1
			ORDER BY
					[dbo].[au_StudentCurriculumStatus].[ViewOrder]
		END
END 
GO

/*
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.au_getStatusEnum 1 
GO
*/